<?php
/**
 * Statistic
 *
 * Формирование статистики
 *
 * @author Дмитрий Кузьма <kouzma.d@gmail.com>
 * @version 1.0
 */
class Statistic  {

	/**
	 * Получает статистику по бронированиям на букинге сразу за все периоды по месяцам
	 * 
	 */
	public static function getBookingsStatsByMonthes() {
		Service_Debug::setDebug();
		$arrRet = array();
		$arrCurDate = getdate(Service_Date::gTime());
		$arrEndDate = $arrCurDate;
		$arrEndDate['mday'] = 1;
		if ($arrEndDate['mon'] <= 11) $arrEndDate['mon']++;
		else {
			$arrEndDate['mon'] = 1;
			$arrEndDate['year']++;
		}
		do {
			$arrStartDate = $arrEndDate;
			if ($arrStartDate['mon'] > 1) $arrStartDate['mon']--;
			else {
				$arrStartDate['mon'] = 12;
				$arrStartDate['year']--;
			}
			$startDateDB = Service_Date::dateToBDFromArr($arrStartDate);
			$arrRet[$startDateDB] = self::getBookingsStatForPeriod($startDateDB, Service_Date::dateToBDFromArr($arrEndDate));
			$arrEndDate = $arrStartDate;
			
		} while ($arrStartDate['year'] >= 2012);
		return $arrRet;
	}
	/**
	 * Получает статистику заказаов за период
	 * 
	 * @param string $periodStart - дата в формате в БД. Начиная с нее идет отсчет включительно
	 * @param string $periodEnd - дата в формате в БД. До нее идет отсчет НЕ включительно
	 */
	public static  function getBookingsStatForPeriod($periodStart = '2012-03-01', $periodEnd = '2012-04-01') {
		Service_Debug::setDebug();
		$objDB = Service_Database::instance();
	  // Список всех бронирвоаний за период
	  //SUM(`order`.`rub_price`) AS `total_price`
	  $stmt = "SELECT 
	  	COUNT(`booking`.`ID_booking`) AS `amount`, 
			SUM(`booking`.`b_com_total_cost`) AS `total_price`,
	  	SUM(`b_com_euro_fee`) AS `total_comission`,
  		SUM(IF(`booking`.`b_com_status` = 'C', 0, 1)) AS `not_cancelled_amount`,
  		SUM(IF(`booking`.`b_com_status` = 'C', 0, `b_com_euro_fee`)) AS `not_cancelled_comission`,
	  	SUM(IF(`booking`.`b_com_status` = 'C', 0, `booking`.`b_com_total_cost`)) AS `not_cancelled_price`,
  		SUM(IF(`booking`.`is_cancelled` AND `booking`.`cancelled_at` < '" . $periodEnd . "', 0, 1)) AS `not_cancelled_amount_period`,
	  	SUM(IF(`booking`.`is_cancelled` AND `booking`.`cancelled_at` < '" . $periodEnd . "', 0, `booking`.`b_com_total_cost`)) AS `not_cancelled_price_period`,
	  	SUM(IF(`booking`.`is_cancelled` AND `booking`.`cancelled_at` < '" . $periodEnd . "', 0, `b_com_euro_fee`)) AS `not_cancelled_comission_period`,
	  	SUM(IF(`booking`.`b_com_status` != 'C' AND `booking`.`b_com_fee_calculation_date` IS NOT NULL, 1, 0)) AS `accepted_amount`,
	  	SUM(IF(`booking`.`b_com_status` != 'C' AND `booking`.`b_com_fee_calculation_date` IS NOT NULL, `b_com_euro_fee`, 0)) AS `accepted_price`,
	  	SUM(IF(`booking`.`b_com_status` != 'C' AND `booking`.`b_com_fee_calculation_date` IS NOT NULL AND `booking`.`b_com_creditslip_id` IS NOT NULL, 1, 0)) AS `accepted_absolute_amount`,
	  	SUM(IF(`booking`.`b_com_status` != 'C' AND `booking`.`b_com_fee_calculation_date` IS NOT NULL AND `booking`.`b_com_creditslip_id` IS NOT NULL, `b_com_euro_fee`, 0)) AS `accepted_absolute_price`
	  FROM `booking` 
	  LEFT JOIN (
	  		SELECT `booking_block`.`price` / `currency_exchange`.`exchange` AS `rub_price`, `booking_block`.`ID_booking`
	  		FROM `booking_block`
	  		INNER JOIN `currency_exchange` ON (`booking_block`.`currency` = `currency_exchange`.`currency_code`)
	  		GROUP BY `booking_block`.`ID_booking`
	  	) `order` 
	  	ON `booking`.`ID_booking` = `order`.`ID_booking`
	  WHERE 
	  	`booking`.`created_at` >= '" . $periodStart . "' AND 
	  	`booking`.`created_at` < '" . $periodEnd . "' AND
	  	`booking`.`payment_type` != 'Another' AND
	  	`booking`.`is_our` = 0  AND
	  	(`booking`.`cancelled_at` IS NULL OR TIMESTAMPDIFF (HOUR, created_at, cancelled_at) >= 24)
	  ";
	  $arrStatForPeriod = $objDB->selectOne($stmt);
	  
	  // Информация по заказам Extranet
	  $stmt = "SELECT 
	  	COUNT(`booking`.`ID_booking`) AS `amount`, 
			SUM(`booking`.`full_price`) AS `total_price`,
			SUM(`booking`.`comission`) AS `total_comission`,
  		SUM(IF(`booking`.`is_cancelled` = '1', 0, 1)) AS `not_cancelled_amount`,
  		SUM(IF(`booking`.`is_cancelled` = '1', 0, `comission`)) AS `not_cancelled_comission`,
	  	SUM(IF(`booking`.`is_cancelled` = '1', 0, `booking`.`full_price`)) AS `not_cancelled_price`
	  FROM `booking` 
	  WHERE 
	  	`booking`.`created_at` >= '" . $periodStart . "' AND 
	  	`booking`.`created_at` < '" . $periodEnd . "' AND
	  	`booking`.`payment_type` != 'Another' AND
	  	`booking`.`is_our` = 1  AND
	  	(`booking`.`cancelled_at` IS NULL OR TIMESTAMPDIFF (HOUR, created_at, cancelled_at) >= 24)
	  ";
	  $arrExtranetStatForPeriod = $objDB->selectOne($stmt);
	  
	  // Количество человеконочей всего
	  $stmt = "SELECT SUM(`booking_block`.`guest_qty` * TIMESTAMPDIFF (DAY, `date_arr`, `date_dep`)) AS `man_nights`
	  FROM `booking`
	  LEFT JOIN `booking_block` USING(`ID_booking`)
	  WHERE
	  	`booking`.`created_at` >= '" . $periodStart . "' AND 
	  	`booking`.`created_at` < '" . $periodEnd . "' AND
	  	`booking`.`e_mail` NOT IN ('slamer86@gmail.com', 'dmkouzma@gmail.com')
	  ";
	  $manNights = $objDB->selectSimpleField($stmt);
	  
	  // Количество все всех бронирований
	  $stmt = "SELECT 
	  	COUNT(`booking`.`ID_booking`) AS `total_amount`,
	  	SUM(IF(`booking`.`payment_type` = 'Another', 1, 0)) AS `offline_amount` 
	  FROM `booking`
	  WHERE
	  	`booking`.`created_at` >= '" . $periodStart . "' AND 
	  	`booking`.`created_at` < '" . $periodEnd . "' AND
	  	`booking`.`e_mail` NOT IN ('slamer86@gmail.com', 'dmkouzma@gmail.com')
	  ";
	  $arrBookingsAmount = $objDB->selectOne($stmt);
	  
	  Service_Exchange::setCurrency('EUR');
	  $objExchange = Service_Exchange::instance();
	  $arrStatForPeriod['man_nights'] = intval($manNights);
	  $arrStatForPeriod['total_amount'] = intval($arrBookingsAmount['total_amount']);
	  $arrStatForPeriod['offline_amount'] = intval($arrBookingsAmount['offline_amount']);
	  $arrStatForPeriod['total_price'] = $objExchange->getShortPrice($arrStatForPeriod['total_price'], 'EUR');
	  $arrStatForPeriod['not_cancelled_price'] = $objExchange->getShortPrice($arrStatForPeriod['not_cancelled_price'], 'EUR');
	  $arrStatForPeriod['not_cancelled_price_period'] = $objExchange->getShortPrice($arrStatForPeriod['not_cancelled_price_period'], 'EUR');
	  $arrStatForPeriod['average_price'] = $arrStatForPeriod['amount'] ? $arrStatForPeriod['total_price'] / $arrStatForPeriod['amount'] : 0;
	  $arrStatForPeriod['not_cancelled_average_price'] = $arrStatForPeriod['not_cancelled_amount'] ? $arrStatForPeriod['not_cancelled_price'] / $arrStatForPeriod['not_cancelled_amount'] : 0;
	  $arrStatForPeriod['not_cancelled_average_price_period'] = $arrStatForPeriod['not_cancelled_amount_period'] ? $arrStatForPeriod['not_cancelled_price_period'] / $arrStatForPeriod['not_cancelled_amount_period'] : 0;
	  $arrStatForPeriod['extranet'] = $arrExtranetStatForPeriod;
	  return $arrStatForPeriod;
	}
	
/*
количество и сумму бронирований за апрель, не аннулированных в тот же день, ср сумму. (если в этот день сделано и аннулировано то не считается, остальные считаются)

количество и сумму бронирований за апрель не аннулированных на 31 апреля

процент аннуляции

Прибыль Букинги Конверсия в букинги % Ср сумма букинга в евро Процент аннуляции в долл Комиссия в отеле Наша доля комиссии
  */
}
?>